﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;

namespace md5Tools
{
    internal class EPPlusTools
    {
        public String filePath { get; set; } = "";
        public ExcelPackage excelPackage { get; set; }
        public ExcelWorkbook excelWorkbook { get; set; }
        public ExcelWorksheet excelWorksheet { get; set; }


        public EPPlusTools()
        {
            //用于未激活报错
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        }
        /// <summary>
        /// 创建模板文件
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public String createTemplate(String entity = null, String path = null, Boolean temp = false)
        {
            String name = (temp ? $"temp_{PublicTools.nowString()}" : PublicTools.nowString());

            if (String.IsNullOrEmpty(path))
            {
                String folder = "data";
                if (temp)
                {
                    folder += $@"\temp";
                }
                //检查当前文件夹下是否存在 data文件夹，并生成该文件夹
                if (!Directory.Exists(folder))
                {
                    Directory.CreateDirectory(folder);
                }
                path = $@"{folder}\{name}.xlsx";
            }
            excelPackage = new ExcelPackage(new FileInfo(path));
            excelWorkbook = excelPackage.Workbook;
            excelWorksheet = excelWorkbook.Worksheets.Add(PublicTools.nowString());

            //加粗单元格
            excelWorksheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            excelWorksheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            excelWorksheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            excelWorksheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            //设置字体样式
            excelWorksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
            excelWorksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
            excelWorksheet.View.ShowGridLines = false;//去掉sheet的网格线
            // PR寄存器的模板
            if ("PR".Equals(entity))
            {
                //合并单元格
                excelWorksheet.Cells["C1:K1"].Merge = true;
                excelWorksheet.Cells["L1:T1"].Merge = true;
                excelWorksheet.Cells["U1:AC1"].Merge = true;

                //设置颜色
                excelWorksheet.Cells["A:B"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                excelWorksheet.Cells["A:B"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(129, 228, 255));
                excelWorksheet.Cells["C:K"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                excelWorksheet.Cells["C:K"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 0));
                excelWorksheet.Cells["L:T"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                excelWorksheet.Cells["L:T"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(129, 227, 255));
                excelWorksheet.Cells["U:AC"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                excelWorksheet.Cells["U:AC"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 243, 202));
                excelWorksheet.Cells["AD:AD"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                excelWorksheet.Cells["AD:AD"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 243, 102));
                //添加表头
                excelWorksheet.Cells["A1"].SetCellValue(0, 0, "序号");
                excelWorksheet.Cells["A1"].SetCellValue(0, 1, "组号");
                excelWorksheet.Cells["A1"].SetCellValue(0, 2, "配置[C1{0:N,1:F},C2{未知},C3{0:D,1:U},C4{0:B,1:T}]");
                excelWorksheet.Cells["A1"].SetCellValue(0, 11, "世界");
                excelWorksheet.Cells["A1"].SetCellValue(0, 20, "关节");
                excelWorksheet.Cells["A1"].SetCellValue(0, 29, "PR注释");
            }
            // R寄存器的模板
            if ("R".Equals(entity))
            {
                //添加表头
                excelWorksheet.Cells["A1"].SetCellValue(0, 0, "序号");
                excelWorksheet.Cells["A1"].SetCellValue(0, 1, "值");
                excelWorksheet.Cells["A1"].SetCellValue(0, 2, "注解");
            }
            //excelWorksheet.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
            excelPackage.Save();
            filePath = path;
            return path;
        }

        /// <summary>
        /// 添加数据到表单（DataTable）
        /// </summary>
        /// <param name="table">数据</param>
        /// <param name="obj">excel单元格坐标</param>
        /// <param name="FreezePanes">冻结窗口[1,1]</param>
        /// <returns></returns>
        public bool WriteDataTable(DataTable table, String obj, int[] FreezePanes)
        {
            excelWorksheet.Cells[(obj != null) ? obj : "A1"].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
            // 固定行
            if (FreezePanes != null && FreezePanes.Count() > 0)
            {
                excelWorksheet.View.FreezePanes(FreezePanes[0], FreezePanes[1]);
            }
            return true;
        }
        /// <summary>
        /// 设置字体颜色
        /// </summary>
        /// <param name="color"></param>
        /// <returns></returns>
        public bool setTextColor(Color color)
        {
            // 字体颜色
            excelWorksheet.Cells.Style.Font.Color.SetColor(color);
            return true;
        }

        public void saveExecel(String path)
        {
            excelPackage.SaveAs(path);
        }
        /// <summary>
        /// 打开文件
        /// </summary>
        /// <param name="path"></param>
        public void openFile(String path)
        {
            Process.Start("explorer.exe", path);
        }




    }
}
